<?php
/**
 * 統計画面コントローラー
 *
 * @package  app
 * @extends  Controller
 */
class Controller_Manage_Analyze extends Controller_Manage {
	public $paths = array();
	public $mongo_api;
	
	/**
	 * before
	 *
	 * @access  public
	 * @return  Response
	 */
	public function before() {
		parent::before();
		
		$m = new Mongo('localhost');
		$this->mongo_api = $m->selectDB('commesodo')->selectCollection('api');

		$this->paths = explode('/', Input::uri());
	}

	/**
	 * index
	 *
	 * @access  public
	 * @return  Response
	 */
	public function action_index() {
		return Response::forge(View::forge('manage/analyze/index'), D_HTTP_UPDATE);
	}

	/**
	 * デイリーレポート
	 *
	 * @access  public
	 * @return  Response
	 */
	public function action_daily() {
		$this->paths[3] = 'daily';
		return $this->action_report();
	}
	
	/**
	 * レポート
	 *
	 * @access  public
	 * @return  Response
	 */
	public function action_report() {
		$description = '';
		$list = array();
		
		for( $i = 0; $i < 30; $i ++) {
			$col = array();

			switch( $this->paths[3] ) {
				case 'daily':
					$day = 60 * 60 * 24;
					$max = mktime(0,0,0,date("m"),date("d"),date("Y")) - ($i - 1) * $day;
					$min = $max - $day;
					$col['日付'] = date('Y/m/d', $min);
					break;
				case 'monthly':
					$max = mktime(0,0,0,date("m")-$i+1,1,date("Y"));
					$min = mktime(0,0,0,date("m")-$i  ,1,date("Y"));
					$col['日付'] = date('Y/m', $min);
					break;
			}

			
			// インストール数
			$query = DB::select(DB::expr("COUNT(*) AS c"));
			$query -> from('member_info');
			$query -> where('create_date', '<',  date('Y-m-d', $max));
			$query -> where('create_date', '>=', date('Y-m-d',$min));
			$result = $query -> execute() -> as_array();
			$col['ｲﾝｽﾄｰﾙ'] = $result[0]['c'];
			
			// 起動数
			$result = $this->mongo_api->aggregate(array(
				array(
					'$match' => array(
						'member_id',
						'time' => array(
							'$gte' => new MongoDate($min - 60 * 9),
							'$lte' => new MongoDate($max - 60 * 9),
							),
						),
					),
					array(
						'$group' => array(
						'_id' => array('member_id' => '$member_id'),
						'count' => array('$sum' => '1'),
						),
					),
				));
			$member_id = array();
			foreach( $result['result'] as $v) {
				$member_id[$v['_id']['member_id']] = 1;
			}
			// 認証後、サーバー通信していない取りこぼしをカウント
			$uuid = $this->mongo_api->aggregate(array(
				array(
					'$match' => array(
						'uri' => 'api/member/auth/',
						'time' => array(
							'$gte' => new MongoDate($min - 60 * 9),
							'$lte' => new MongoDate($max - 60 * 9),
							),
						),
					),
					array(
						'$group' => array(
							'_id' => array('uuid' => '$uuid'),
							'count' => array('$sum' => '1'),
						),
					),
				));
			foreach( $uuid['result'] as $v) {
				$query = DB::select('member_id');
				$query -> from('member_info');
				$query -> where('uuid', $v['_id']['uuid']);
				$result = $query -> execute() -> as_array();
				if( count($result) > 0 ) {
					$member_id[$result[0]['member_id']] = 1;
				}
			}
			$col['起動UU'] = count($member_id);
			
			// ポイント獲得数
			$query = DB::select('member_point.member_id');
			$query -> from('member_point');
			$query -> where_open();
			$query -> where('key1', 'appdrv');
			$query -> or_where('reward_id', '>', '0');
			$query -> where_close();
			$query -> where('member_point.create_date', '<',  date('Y-m-d', $max));
			$query -> where('member_point.create_date', '>=', date('Y-m-d', $min));
			$query -> join('member_info') -> on('member_point.member_id', '=', 'member_info.member_id');
			$query -> group_by('member_point.member_id');
			$query -> where('member_info.create_date', '<',  date('Y-m-d', $min));
			$result = $query -> execute() -> as_array();
			$col['ｱｸｼｮﾝ/UU継続'] = count($result);
			
			$query = DB::select('member_point.member_id');
			$query -> from('member_point');
			$query -> where_open();
			$query -> where('key1', 'appdrv');
			$query -> or_where('reward_id', '>', '0');
			$query -> where_close();
			$query -> where('member_point.create_date', '<',  date('Y-m-d', $max));
			$query -> where('member_point.create_date', '>=', date('Y-m-d', $min));
			$query -> join('member_info') -> on('member_point.member_id', '=', 'member_info.member_id');
			$query -> group_by('member_point.member_id');
			$query -> where('member_info.create_date', '<',  date('Y-m-d', $max));
			$query -> where('member_info.create_date', '>=', date('Y-m-d', $min));
			$result = $query -> execute() -> as_array();
			$col['ｱｸ/UU当日'] = count($result);
			
			$query = DB::select('member_id');
			$query -> from('member_point');
			$query -> where_open();
			$query -> where('key1', 'appdrv');
			$query -> or_where('reward_id', '>', '0');
			$query -> where_close();
			$query -> where('create_date', '<',  date('Y-m-d', $max));
			$query -> where('create_date', '>=', date('Y-m-d', $min));
			$query -> group_by('member_id');
			$today = $query -> execute() -> as_array();
			$count = 0;
			foreach( $today as $t ) {
				$query = DB::select('member_id');
				$query -> from('member_point');
				$query -> where('member_id', $t['member_id']);
				$query -> where_open();
				$query -> where('key1', 'appdrv');
				$query -> or_where('reward_id', '>', '0');
				$query -> where_close();
				$query -> where('create_date', '<',  date('Y-m-d', $min));
				$result = $query -> execute() -> as_array();
				if( count($result) > 0 ) {
					$count ++;
				}
			}
			$col['ｱｸ/UUﾘﾋﾟｰﾄ'] = $count;
			
			// リクエスト数
			$query = DB::select('member_id');
			$query -> from('member_request');
			$query -> where('create_date', '<',  date('Y-m-d', $max));
			$query -> where('create_date', '>=', date('Y-m-d', $min));
			$query -> group_by('member_id');
			$result = $query -> execute() -> as_array();
			$col['ﾘｸｴｽﾄ/UU'] = count($result);
	
			// リクエスト数
			$query = DB::select('member_id');
			$query -> from('member_request');
			$query -> where('create_date', '<',  date('Y-m-d', $max));
			$query -> where('create_date', '>=', date('Y-m-d', $min));
			$result = $query -> execute() -> as_array();
			$col['ﾘｸ/件数'] = count($result);

			//
			$list[] = $col;
		}

		$csv = Input::get('csv');
		if( empty($csv)) {
			return Response::forge(View::forge('manage/table',array('description'=>$description,'list' => $list)), D_HTTP_UPDATE);
		} else {
			return Response::forge(View::forge('manage/csv',array('description'=>$description,'list' => $list)), D_HTTP_UPDATE);
		}
	}
	
	/**
	 * 会員
	 *
	 * @access  public
	 * @return  Response
	 */
	public function action_member() {
		$description = "";
		$action = $this->paths[3];
		
		$periods = array();
		$periods['hourly'] = '%Y/%m/%d %H:00';
		$periods['daily'] = '%Y/%m/%d';
		$periods['monthly'] = '%Y/%m';
		
		$step = array();
		$step['hourly'] = 60 * 60;
		$step['daily'] = $step['hourly'] * 24;
		$step['monthly'] = $step['daily'] * 30;

		switch( $action ) {
			case 'startup':
				$periods = array();
				$periods['hourly'] = 'Y/m/d H:00';
				$periods['daily'] = 'Y/m/d';
				$periods['monthly'] = 'Y/m/01';

				// 起動数
				$step = $step[$this->paths[4]];
				$start = date($periods[$this->paths[4]], time());
				for( $i = 0; $i < 100; $i ++) {
					$d = strtotime($start) - $i * $step;
					$s = new MongoDate($d - 60 * 9);
					$e = new MongoDate($d - 60 * 9 + $step);
					$result = $this->mongo_api->aggregate(array(
					    array(
		   	  				 '$match' => array(
					          	'member_id',
					          	'time' => array(
					          		'$gte' => $s,
					          		'$lte' => $e,
									),
						      ),
					    ),
					    array(
   			   				'$group' => array(
   		    	  				 '_id' => array('member_id' => '$member_id'),
							),
						),
					));
					//if( count($result['result']) == 0 ) break;
					$l = array('起動日時' => date($periods[$this->paths[4]], $d), '起動数' => count($result['result']) );
					$list[] = $l;
				}
				break;
			case 'install':
				// インストール数
				$query = DB::select(DB::expr("DATE_FORMAT(create_date, '" . $periods[$this->paths[4]] . "') AS インストール日時"),DB::expr("COUNT(*) AS インストール数"));
				$query -> from('member_info');
				$query -> group_by('インストール日時');
				$query -> order_by('create_date','DESC');
				$query -> limit(1000);
				$list = $query -> execute() -> as_array();
				break;
			case 'affiliate':
				// アフィリエイト経由
				$query = DB::select(DB::expr("DATE_FORMAT(create_date, '" . $periods[$this->paths[4]] . "') AS インストール日時"),DB::expr('key1 AS アフィリエイト種別'),DB::expr("COUNT(*) AS インストール数"));
				$query -> from('member_action');
				$query -> where('action','installed');
				$query -> group_by('インストール日時','key1');
				$query -> order_by('create_date','DESC');
				$query -> order_by('key1','DESC');
				$query -> limit(1000);
				$list = $query -> execute() -> as_array();
				break;
		}
		
		$csv = Input::get('csv');
		if( empty($csv)) {
			return Response::forge(View::forge('manage/table',array('description'=>$description,'list' => $list)), D_HTTP_UPDATE);
		} else {
			return Response::forge(View::forge('manage/csv',array('description'=>$description,'list' => $list)), D_HTTP_UPDATE);
		}
	}

	/**
	 * ポイント流通
	 *
	 * @access  public
	 * @return  Response
	 */
	public function action_point() {
		$description = '';
		$list = array();
		
		for( $i = 0; $i < 30; $i ++) {
			$col = array();

			switch( $this->paths[3] ) {
				case 'daily':
					$day = 60 * 60 * 24;
					$max = mktime(0,0,0,date("m"),date("d"),date("Y")) - ($i - 1) * $day;
					$min = $max - $day;
					$col['日付'] = date('Y/m/d', $min);
					break;
				case 'monthly':
					$max = mktime(0,0,0,date("m")-$i+1,1,date("Y"));
					$min = mktime(0,0,0,date("m")-$i  ,1,date("Y"));
					$col['日付'] = date('Y/m', $min);
					break;
			}
			
			// ポイント流通
			$query = DB::select(DB::expr("key1 AS 種別"), DB::expr("COUNT(*) AS 件数"), DB::expr("SUM(point_value) AS ポイント合計"), DB::expr("SUM(payment) AS 売上合計"));
			$query -> from('member_point');
			$query -> where('member_point.create_date', '<',  date('Y-m-d', $max));
			$query -> where('member_point.create_date', '>=', date('Y-m-d',$min));
			$query -> join('reward_view','LEFT') -> on('reward_view.reward_id', '=', 'member_point.reward_id');
			$query -> group_by('key1');
			$query -> order_by('key1');
			$result = $query -> execute() -> as_array();
			
			foreach($result as $value) {
				$list[] = array_merge($col, $value);
			}
		}

		$csv = Input::get('csv');
		if( empty($csv)) {
			return Response::forge(View::forge('manage/table',array('description'=>$description,'list' => $list)), D_HTTP_UPDATE);
		} else {
			return Response::forge(View::forge('manage/csv',array('description'=>$description,'list' => $list)), D_HTTP_UPDATE);
		}
	}
}
